IF EXISTS(SELECT name FROM sys.databases WHERE name = 'MatrimonyData')
DROP DATABASE MatrimonyData
GO

CREATE DATABASE	MatrimonyData
GO

USE MatrimonyData
GO

CREATE TABLE Categories
(
	CateID int identity(1,1) primary key,
	CategoryName nvarchar(150) not null,
	ImageUrl nvarchar(150),
	[Description] nvarchar(max),
	DateCreated date not null,
	[Type] int not null,
	[Status] bit default 1
)
GO

INSERT INTO Categories
VALUES('Main Menu', 'images/cate/mainmenu.png', 'This is main menu of Home Page', '09/15/2012', 1, 1);
INSERT INTO Categories
VALUES('Navigation Menu', 'images/cate/navmenu.png', 'This is navigation menu of Home Page', '09/15/2012', 2, 1);
GO

CREATE TABLE Menus
(
	MenuID int identity(1,1) primary key,
	CateID int not null,
	ParentID int,
	ItemOrder int,
	MenuName nvarchar(150) not null,
	MenuLink nvarchar(150) not null,
	ImageUrl nvarchar(150),
	DateCreated datetime not null,
	[Status] bit default 1
)
GO

INSERT INTO Menus
VALUES(1, 0, 1, 'Home', 'home.jsp', 'images/menus/home.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(1, 0, 2, 'Registration', 'register.jsp', 'images/menus/register.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(1, 0, 3, 'Search', 'search.jsp', 'images/menus/search.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(1, 0, 4, 'Contact Us', 'contact.jsp', 'images/menus/contact.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 0, 1, 'Favorites', '#', 'images/menus/favorites.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 0, 2, 'Actions', '#', 'images/menus/account.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 0, 3, 'Utilities', '#', 'images/menus/utilities.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 1, 1, 'Message', '#', 'images/menus/message.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 1, 2, 'Photos', '#', 'images/menus/photos.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 1, 3, 'Your Post', '#', 'images/menus/post.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 1, 4, 'Find Friends', '#', 'images/menus/friend.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 1, 5, 'Events', '#', 'images/menus/event.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 2, 1, 'Profile', '#', 'images/menus/profile.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 2, 2, 'Account Information', '#', 'images/menus/utilities.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 2, 3, 'Payment', '#', 'images/menus/settings.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 2, 4, 'Settings', '#', 'images/menus/utilities.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 3, 1, 'News', '#', 'images/menus/news.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 3, 2, 'Service', '#', 'images/menus/service.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 3, 3, 'Search', '#', 'images/menus/search.png', '09/15/2012', 1);
INSERT INTO Menus
VALUES(2, 3, 4, 'Contact Us', '#', 'images/menus/navcontact.png', '09/15/2012', 1);
GO

CREATE XML SCHEMA COLLECTION XMLBasicDetails AS
	N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	  <xs:element name="BasicDetail">
		<xs:complexType>
		  <xs:sequence>
			<xs:element name="FirstName" type="xs:string" />
			<xs:element name="MiddleName" type="xs:string" />
			<xs:element name="LastName" type="xs:string" />
			<xs:element name="Gender" type="xs:boolean" />
		  </xs:sequence>
		</xs:complexType>
	  </xs:element>
	</xs:schema>'
GO

CREATE XML SCHEMA COLLECTION XMLDateOfBirth AS
	N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	  <xs:element name="DateofBirth">
		<xs:complexType>
		  <xs:sequence>
			<xs:element name="MaritalStatus" type="xs:string" />
			<xs:element name="Height" type="xs:int" />
			<xs:element name="CountryLiving" type="xs:string" />
			<xs:element name="City" type="xs:string" />
			<xs:element name="ContactNumber" type="xs:string"/>
			<xs:element name="MotherTongue" type="xs:string" />
			<xs:element name="Religion" type="xs:string" />
			<xs:element name="Caste" type="xs:string" />
		  </xs:sequence>
		</xs:complexType>
	  </xs:element>
	</xs:schema>'
GO

Create XML Schema Collection XMLFList AS
	N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	  <xs:element name="Friends">
		<xs:complexType>
		  <xs:sequence>
			<xs:element maxOccurs="unbounded" name="Friend" type="xs:string" />
		  </xs:sequence>
		</xs:complexType>
	  </xs:element>
	</xs:schema>'
Go

CREATE XML SCHEMA COLLECTION XMLGList AS
	N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	  <xs:element name="Gallerys">
		<xs:complexType>
		  <xs:sequence>
			<xs:element maxOccurs="unbounded" name="Gallery">
			  <xs:complexType>
				<xs:sequence>
				  <xs:element maxOccurs="unbounded" name="item" type="xs:string" />
				</xs:sequence>
				<xs:attribute name="Name" type="xs:string" use="required" />
			  </xs:complexType>
			</xs:element>
		  </xs:sequence>
		</xs:complexType>
	  </xs:element>
	</xs:schema>'
GO

CREATE XML SCHEMA COLLECTION XMLReligion AS
	N'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
	  <xs:element name="Religions">
		<xs:complexType>
		  <xs:sequence>
			<xs:element maxOccurs="unbounded" name="Religion">
			  <xs:complexType>
				<xs:sequence minOccurs="0">
				  <xs:element maxOccurs="unbounded" name="State" type="xs:string" />
				</xs:sequence>
				<xs:attribute name="Name" type="xs:string" use="required" />
			  </xs:complexType>
			</xs:element>
		  </xs:sequence>
		</xs:complexType>
	  </xs:element>
	</xs:schema>'
GO

CREATE TABLE Users
(
	UNID nvarchar(20) primary key,
	UNPassword nvarchar(50) not null,
	UNEmail nvarchar(50) not null,
	UNBasicDetails xml (XMLBasicDetails) not null,
	UNDateOfBirth xml (XMLDateOfBirth) not null,
	UNRegistration bit default 'False',
	UNStatus bit default 'True'
)
GO

INSERT INTO Users
VALUES ('admin', '1234567', 'dinhthao.aptech@gmail.com', 
		N'<?xml version="1.0" encoding="UTF-16"?>
		    <BasicDetail>
				<FirstName>Thao</FirstName>
				<MiddleName>Dinh</MiddleName>
				<LastName>Nguyen</LastName>
				<Gender>1</Gender>
		    </BasicDetail>', 
		N'<?xml version="1.0" encoding="UTF-16"?>
		    <DateofBirth>
				<MaritalStatus>Widowed</MaritalStatus>
				<Height>50</Height>
				<CountryLiving>Viet Nam</CountryLiving>
				<City>Ho Chi Minh</City>
				<ContactNumber>01227500512</ContactNumber>
				<MotherTongue>Viet Nam</MotherTongue>
				<Religion>No</Religion>
				<Caste>Tribal chief</Caste>
		    </DateofBirth>', 
		1, 1)
GO

CREATE TABLE Account
(
	AccountID int identity(1,1) primary key,
	UNID nvarchar(20) not null,
	PaymentDate datetime not null,
	Expires datetime not null,
	DaysRemaining int not null,
	[Type] int not null,
	[Status] bit default 0
)
GO

CREATE TABLE LimitCategory
(
	LimitCateID int identity(1,1) primary key,
	LimitField nvarchar(100) not null,
	[Status] bit default 1
)
GO

CREATE TABLE LimitSettings
(
	UNID nvarchar(20) primary key,
	LimitCateID int not null,
	Remaining int not null
)
GO

CREATE TABLE Friends
(
	FID int identity primary key,
	IDUN nvarchar(20) not null,
	FList xml (XMLFList) not null,
	FStatus bit default 'True'
)
GO
--
CREATE TABLE GroupUsers
(
	GroupID int identity(1,1) primary key,
	UNID nvarchar(20) not null,
	FriendAdded nvarchar(20) not null
)
GO

CREATE TABLE  Galleries
(
	GID int identity primary key,
	IDUN nvarchar(20) not null,
	GList xml (XMLGList) not null,
	GStatus bit default 'True'
)
GO

CREATE TABLE Images
(
	ImageID int identity(1,1) primary key,
	ImageName  NVARCHAR(200) NOT NULL,
	ImageUrl   NVARCHAR(200) NOT NULL,
	[Status]   BIT DEFAULT 1
)
GO

--Co The Bo--
CREATE TABLE PhotosAlbum
(
	PhotosID         INT PRIMARY KEY IDENTITY,
	IDImage    INT NOT NULL,
	IDGallery  INT NOT NULL,
	IDUsers nvarchar(20) not null
)
GO
----xml cho comment
CREATE TABLE Post
(
	PostID int identity(1,1) primary key,
	UNID nvarchar(20) not null,
	PostContent nvarchar(max),
	PostStatus bit not null
)
GO
CREATE TABLE Comments
(
	CommentID int identity(1,1) primary key,
	UNID nvarchar(20) not null,
	PostID int not null,
	Content nvarchar(max),
	DatePost datetime,
)
GO

CREATE TABLE [Messages]
(
	MessageID int identity(1,1) primary key,
	UNID nvarchar(20) not null,
	Friend nvarchar(20) not null,
	MessageContent nvarchar(max),
	DatePost datetime,
)
GO

CREATE TABLE Contacts
(
	ContactID int identity(1,1) primary key,
	CateID int not null,
	[Subject] nvarchar(150) not null,
	FullName nvarchar(150) not null,
	Email nvarchar(150) not null,
	Phone nvarchar(13),
	[Address] nvarchar(150),
	Contents nvarchar(max) not null,
	IPClient nvarchar(100),
	DateCreated datetime not null,
	[Status] bit default 0
)
GO

CREATE TABLE FAQ
(
	FAQID int identity(1,1) primary key,
	UNID nvarchar(20) not null,
	FaqTitle nvarchar(150) not null,
	FaqDescription nvarchar(max) not null,
	FaqStatus bit default 0
)
GO

CREATE TABLE Partners
(
	PartnerID int identity(1,1) primary key,
	PartnerName nvarchar(150) not null,
	[Address] nvarchar(150) not null,
	PhoneNumber nvarchar(12) not null,
	Email nvarchar(150),
	Website nvarchar(150),
	ImageUrl nvarchar(150),
	Scope nvarchar(50),
	[Description] nvarchar(max),
	[Status] bit default 1
)
GO

CREATE TABLE [Services]
(
	ServiceID int identity(1,1) primary key,
	CateID int not null,
	Title nvarchar(100) not null,
	Edition nvarchar(100) not null,
	ViewType int not null,
	TypeName nvarchar(100),
	Link nvarchar(150),
	FileType nvarchar(100) not null,
	PartnerName nvarchar(150) not null,
	Email nvarchar(150) not null,
	Phone nvarchar(13) not null,
	Fax nvarchar(13),
	CompanyName nvarchar(150),
	[Site] nvarchar(100),
	DatePost datetime not null,
	Expires datetime not null,
	IPClient nvarchar(100),
	PaymentStatus bit default 0,
	[Status] bit default 0
)
GO

CREATE TABLE YourAdorSponsoredStory
(
	AdID int identity primary key,
	IDUN nvarchar(20) not null,
	AdTitle nvarchar(50) not null,
	AdLink varchar(255) not null,
	AdContent nvarchar(90) not null,
	AdImage varchar(50) not null,
	AdReligion xml (XMLReligion) not null,
	AdStatus bit default 'False'
)
GO

CREATE TABLE NewsCategories
(
	NewsCateID int identity(1,1) primary key,
	NewsCateName nvarchar(150) not null,
	ImageUrl nvarchar(150),
	[Description] nvarchar(max),
	DateCreated date not null,
	[Type] int not null,
	[Status] bit default 1
)
GO

CREATE TABLE News
(
	NewsID int identity(1,1) primary key,
	NewsCateID int not null,
	UNID nvarchar(20) not null,
	NewsTitle nvarchar(150) not null,
	ImageUrl nvarchar(150) not null,
	NewsSummary nvarchar(max) not null,
	NewsContent nvarchar(max) not null,
	Author nvarchar(100) not null,
	DateCreated datetime not null,
	[Status] bit default 1
)
GO

CREATE TABLE SiteDefaultSettings
(
	SettingID int identity(1,1) primary key,
	SettingField nvarchar(150) not null,
	SettingValue nvarchar(150),
)
GO

CREATE TABLE SiteSettings
(
	UNID nvarchar(20) primary key,
	SettingField nvarchar(150) not null,
	SettingValue nvarchar(150),
)
GO

--/////ALTER TABLES----------------

ALTER TABLE Menus
ADD CONSTRAINT FK_Menus_Categories
FOREIGN KEY(CateID)
REFERENCES Categories(CateID)
GO

ALTER TABLE Account
ADD CONSTRAINT FK_Account_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID)
GO

ALTER TABLE Friends 
ADD CONSTRAINT FK_Friends_Users
FOREIGN KEY(IDUN) 
REFERENCES Users(UNID)
GO
----------------------------------------
ALTER TABLE GroupUsers
ADD CONSTRAINT FK_GroupUsers_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID),
FOREIGN KEY(FriendAdded)
REFERENCES Users(UNID)
GO

ALTER TABLE Galleries 
ADD CONSTRAINT FK_Galleries_Users 
FOREIGN KEY(IDUN) 
REFERENCES Users(UNID)
GO

ALTER TABLE Post
ADD CONSTRAINT FK_Post_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID)
GO

ALTER TABLE [Messages]
ADD CONSTRAINT FK_Messages_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID),
FOREIGN KEY(Friend)
REFERENCES Users(UNID)
GO

ALTER TABLE Contacts
ADD CONSTRAINT FK_Contacts_Categories
FOREIGN KEY(CateID)
REFERENCES Categories(CateID)
GO

ALTER TABLE FAQ
ADD CONSTRAINT FK_FAQ_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID)
GO

ALTER TABLE [Services]
ADD CONSTRAINT FK_Services_Categories
FOREIGN KEY(CateID)
REFERENCES Categories(CateID)
GO

ALTER TABLE YourAdorSponsoredStory 
ADD CONSTRAINT FK_YourAdorSponsoredStory_Users 
FOREIGN KEY(IDUN) 
REFERENCES Users(UNID)
GO

ALTER TABLE SiteSettings
ADD CONSTRAINT FK_SiteSettings_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID)
GO

ALTER TABLE LimitSettings
ADD CONSTRAINT FK_LimitSettings_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID),
CONSTRAINT FK_LimitSettings_LimitCategory
FOREIGN KEY(LimitCateID)
REFERENCES LimitCategory(LimitCateID)
GO

ALTER TABLE PhotosAlbum
ADD CONSTRAINT FK_PhotosAlbum_Images
FOREIGN KEY(IDImage)
REFERENCES Images(ImageID),
CONSTRAINT FK_PhotosAlbum_Galleries
FOREIGN KEY(IDGallery)
REFERENCES Galleries(GID)
GO

ALTER TABLE Comments
ADD CONSTRAINT FK_Comments_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID),
CONSTRAINT FK_Comments_Post
FOREIGN KEY(PostID)
REFERENCES Post(PostID)
GO

ALTER TABLE News
ADD CONSTRAINT FK_News_NewsCategories
FOREIGN KEY(NewsCateID)
REFERENCES NewsCategories(NewsCateID),
CONSTRAINT FK_News_Users
FOREIGN KEY(UNID)
REFERENCES Users(UNID)
GO